--2번
create table memberInfo(
	id varchar2(50) primary key,
	password varchar2(50) not null,
	name varchar2(50) not null,
	address varchar2(50) not null,
	floatingPoint number default 0,
	fixedPoint number default 0,
	nick varchar2(50) unique,
	gradeNo number default 1,
	constraint fk1_gradeNo foreign key(gradeNo) references gradeInfo(gradeNo) ON DELETE CASCADE
)
drop table memberInfo;


--1번
create table gradeInfo(
	gradeNo number primary key,
	gradeName varchar2(50) not null,
	minPoint number not null,
	maxPoint number not null
)
drop table gradeInfo;
/*
 * 0 - F - 0 ~ 9999
 * 1 - A - 0 ~ 99
 * 2 - S - 100 ~ 499
 * 3 - VIP - 500 ~ 9999
 * 4 - SubManager - 0 ~ 9999
 * 5 - Manager - 0 ~ 9999
 */

--3번
create table postInfo(
	postNo number primary key,
	id varchar2(50) not null,
	genreNo number not null,
	title varchar2(100) not null,
	writeDate date default sysdate,
	viewCount number default 0,
	boardNo number not null,
	content varchar2(1000) not null,
	orgFileName varchar2(100),
	newFileName varchar2(100),
	constraint fk2_id foreign key(id) references memberInfo(id) ON DELETE CASCADE,
	constraint fk2_genreNo foreign key(genreNo) references genreInfo(genreNo),
	constraint fk2_boardNo foreign key(boardNo) references boardInfo(boardNo) ON DELETE CASCADE
)
create sequence seq_postNo;
drop sequence seq_postNo;
drop table postInfo;


--4번
create table replyInfo(
	replyNo number primary key,
	id varchar2(50) not null,
	postNo number not null,
	constraint fk3_id foreign key(id) references memberInfo(id) ON DELETE CASCADE,
	constraint fk3_postNo foreign key(postNo) references postInfo(postNo) ON DELETE CASCADE,
	content varchar2(1000) not null,
	writeDate date default sysdate
)
create sequence seq_replyNo;
drop sequence seq_replyNo;
drop table replyInfo;


--5번
create table boardInfo(
	boardNo number primary key,
	boardName varchar2(50) not null
)
/*
 * 11 - 자유게시판(freeboard)
 * 12 - Q&A게시판(qnaboard)
 * 13 - 동영상게시판(movieboard)
 * 14 - 요청게시판(requestboard)
 * 15 - 리뷰게시판(reviewboard)
 * 16 - 소식 및 뉴스(newsboard)
 * 17 - 스페셜메뉴(specialboard)
 * 18 - 공지사항(noticeboard)
 * 20 - 음악공유게시판(shareboard)
 */
drop table boardInfo;

--0번
create table genreInfo(
	genreNo number primary key,
	genreName varchar2(50) not null
)
/*
 * 0 - 없음
 * 51 - 발라드
 * 52 - RnB
 * 53 - Rock
 * 54 - HipHop
 * 55 - dance
 * 56 - OST
 */
drop table genreInfo;

--6번
create table checkAttend(
	checkDate date not null,
	id varchar2(50) not null,
	constraint fk4_id foreign key(id) references memberInfo(id) ON DELETE CASCADE,
	constraint pk_check primary key(checkDate,id)
)
drop table checkAttend;


create table guestBook(
	guestBookNo number primary key,
	id varchar2(50) not null,
	constraint fk5_id foreign key(id) references memberInfo(id) ON DELETE CASCADE,
	content varchar2(1000) not null,
	writeDate date default sysdate
)
create sequence seq_guestBookNo;
drop sequence seq_guestBookNo;
drop table guestBook;




select distinct genreNo, genreName from genreInfo;

select p.PostNo, m.name, p.title, p.writeDate, p.viewCount 
from postInfo p, memberInfo m where p.id=m.id;


insert into gradeInfo(gradeNo, gradeName, minPoint, maxPoint) values(0, 'F', 9999, 9999);
insert into gradeInfo(gradeNo, gradeName, minPoint, maxPoint) values(1, 'A', 0, 99);
insert into gradeInfo(gradeNo, gradeName, minPoint, maxPoint) values(2, 'S', 100, 499);
insert into gradeInfo(gradeNo, gradeName, minPoint, maxPoint) values(3, 'VIP', 500, 9999);
insert into gradeInfo(gradeNo, gradeName, minPoint, maxPoint) values(4, 'SubManager', 9999, 9999);
insert into gradeInfo(gradeNo, gradeName, minPoint, maxPoint) values(5, 'Manager', 9999, 9999);

insert into GENREINFO(genreNo, genreName) values(0, 'none');
insert into GENREINFO(genreNo, genreName) values(51, 'Ballad');
insert into GENREINFO(genreNo, genreName) values(52, 'RnB');
insert into GENREINFO(genreNo, genreName) values(53, 'Rock');
insert into GENREINFO(genreNo, genreName) values(54, 'Hiphop');
insert into GENREINFO(genreNo, genreName) values(55, 'Dance');
insert into GENREINFO(genreNo, genreName) values(56, 'OST');

insert into boardInfo(boardNo, boardName) values(11, 'freeboard');
insert into boardInfo(boardNo, boardName) values(12, 'qnaboard');
insert into boardInfo(boardNo, boardName) values(13, 'movieboard');
insert into boardInfo(boardNo, boardName) values(14, 'requestboard');
insert into boardInfo(boardNo, boardName) values(15, 'reviewboard');
insert into boardInfo(boardNo, boardName) values(16, 'newsboard');
insert into boardInfo(boardNo, boardName) values(17, 'specialboard');
insert into boardInfo(boardNo, boardName) values(18, 'noticeboard');
insert into boardInfo(boardNo, boardName) values(20, 'shareboard');
insert into boardInfo(boardNo, boardName) values(21, 'imageboard');
update boardInfo set boardName='specialboard' where boardNo=17;

insert into memberInfo(id, password, name, address, floatingPoint, fixedPoint, nick, gradeNo) values('ajax', '1234', '자바', '판교', 500, 500, 'javak',1);
insert into memberInfo(id, password, name, address, floatingPoint, fixedPoint, nick, gradeNo) values('abcd', 'abcd', '자바', '판교', 500, 500, 'java',1);

insert into postInfo(postNo, id, genreNo, title, boardNo, content) values(seq_postNo.nextval, 'java', 0, 'asds', 11, 'asdsds');
select * from postInfo
select * from memberInfo
select * from replyInfo

insert into memberInfo(id, password, name, address, floatingPoint, fixedPoint, nick, gradeNo) values('qwew', 'qwew', 'qwew', '판교', 500, 500, 'qwew', 5);


-----------------쪽지--------------------
create table message(
	messageNo number primary key,
	sendId varchar2(50) not null,
	content varchar2(500) not null,
	getId varchar2(50) not null,
	constraint fk_sendId foreign key(sendId) references memberInfo(id) ON DELETE CASCADE,
	constraint fk_getId foreign key(getId) references memberInfo(id) ON DELETE CASCADE,
	writeDate date default sysdate,
	sendDelete number default 0,
	getDelete number default 0
);
create sequence seq_messageNo nocache;

drop table message;
drop sequence seq_messageNo;

------다운로드------------
create table report(
	postNo number not null,
	cnt number not null,
	constraint fk6_postNo foreign key(postNo) references postInfo(postNo) on DELETE CASCADE
)

select * from report;
drop table report

----- 추천 -----
create table recommend(
	id varchar2(50) not null,
	postNo number not null,
	checkR varchar2(20) not null,
	constraint fk7_id foreign key(id) references memberInfo(id) ON DELETE CASCADE,
	constraint fk7_postNo foreign key(postNo) references postInfo(postNo) ON DELETE CASCADE,
	constraint pk_recommend primary key(id,postNo) 
	)
drop table recommend